package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sun.jndi.cosnaming.IiopUrl.Address;

import pojo.Customer;
import pojo.UserAddress;
import util.DataConn;

public class UserAddressDao {
	private ComboPooledDataSource ds=new ComboPooledDataSource();
	private QueryRunner qr=new QueryRunner(ds);
	public List<UserAddress> getUserAddressByUserId(String c_name){
		List<UserAddress> list=new ArrayList<UserAddress>();
		String sql="select * from useraddress where c_name=?";
		 try {
				list = qr.query(sql, new BeanListHandler<UserAddress>(UserAddress.class), "xiaohong");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		 return list;
	}
	
	public int  add(UserAddress user){
		int flag=0;
		Connection  conn= DataConn.getConn();
		String sql="insert into useraddress(c_name,name,address,tel) values(?,?,?,?)";
		try{
			PreparedStatement pst= conn.prepareStatement(sql);
			pst.setString(1,user.getC_name());
			pst.setString(2,user.getName());
			pst.setString(3,user.getAddress());
			pst.setString(4,user.getTel());
			flag=pst.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return flag;
		} 
	//修改地址
		public  int  update(UserAddress u){
			int flag=0;
		    Connection conn=DataConn.getConn();
			String sql="update useraddress set c_name=?,name=?,address=?,tel=? where uid=?";
			try {
				PreparedStatement pst= conn.prepareStatement(sql);
				pst.setString(1,u.getC_name());
				pst.setString(2,u.getName());
				pst.setString(3,u.getAddress());
				pst.setString(4,u.getTel());
				pst.setInt(5,u.getUid());
				flag=pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return flag;
		}
		//删除地址
		public  int  del(int uid){
			int flag=0;
		    Connection conn=DataConn.getConn();
			String sql="delete from useraddress where uid=?";
			try {
				PreparedStatement pst= conn.prepareStatement(sql);
				pst.setInt(1,uid);
				flag=pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return flag;}
		//显示地址信息,根据用户
		public List<UserAddress> showUserAddress(String c_name){
			List<UserAddress> list=new ArrayList<UserAddress>();
			  Connection conn=DataConn.getConn();
			  String sql="select * from useraddress where c_name=?";
			  try {
				PreparedStatement pst=conn.prepareStatement(sql);
				pst.setString(1, c_name);
				ResultSet rs= pst.executeQuery();
				UserAddress u; 
				while(rs.next()){
					u=new UserAddress();
				    u.setUid(rs.getInt("uid"));
					u.setC_name(rs.getString("c_name"));
					u.setName(rs.getString(3));
					u.setAddress(rs.getString(4));
					u.setTel(rs.getString(5));
					list.add(u);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;}
		//根据uid显示信息
		public UserAddress getUserAddress(int uid){
			UserAddress u=null;
			Connection conn=DataConn.getConn();
			String sql="select * from useraddress where uid=?";
				try {
					PreparedStatement pst= conn.prepareStatement(sql);
					pst.setInt(1,uid);
					ResultSet rs=pst.executeQuery(); 
					if (rs.next()){
						u=new UserAddress();
						u.setUid(rs.getInt(uid));
						u.setC_name(rs.getString("c_name"));
						u.setName(rs.getString("name"));
						u.setAddress(rs.getString("address"));
						u.setTel(rs.getString("tel")); 
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return u;}
		//显示地址信息
			public List<UserAddress> showUserAddress(){
				List<UserAddress> list=new ArrayList<UserAddress>();
				  Connection conn=DataConn.getConn();
				  String sql="select * from useraddress";
				  try {
					PreparedStatement pst=conn.prepareStatement(sql);
					ResultSet rs= pst.executeQuery();
					UserAddress u; 
					while(rs.next()){
						u=new UserAddress();
					    u.setUid(rs.getInt("uid"));
						u.setC_name(rs.getString("c_name"));
						u.setName(rs.getString(3));
						u.setAddress(rs.getString(4));
						u.setTel(rs.getString(5));
						list.add(u);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;}
}
